﻿/**
 *  [Info]2024-04-08 12:41:41.373:step1 耗时(ms):00:00:01.9297713
    [Info]2024-04-08 12:41:43.039:step2 耗时(ms):00:00:01.6630982
    [Info]2024-04-08 12:41:43.835:step3 耗时(ms):00:00:00.7963916
    2024年4月8日： 使用dataTable写200列，1000行耗时0.7s， 或许该修改方法。
 * 
 * 
 */
using OfficeOpenXml;
using System.Data;
using System.IO;

namespace ZPeng.CSharpPro.Model.Excel
{
    /// <summary>
    /// 提供对 EPPlus的代理，优化接口从而适配当前项目
    /// 通过dataTable做缓存，通过addRows 一次性写多行数据。从而节约时间。200列，1000行写到一个文件中：step4 耗时(ms):00:00:00.6336348
    /// </summary>
    public class EPPlusProxy : IDisposable
    {
        public EPPlusProxy(string dirPath, string fileName)
        {
            _dirPath = dirPath;
            _fileName = fileName;

            if (!Directory.Exists(dirPath))
            {
                Directory.CreateDirectory(dirPath);
            }

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            _fileNo = 0;
            _excelPackage = new ExcelPackage(new FileInfo(_dirPath + "\\" + fileName));

            if (_excelPackage.Workbook.Worksheets.Count >= 0)
            {
                _workSheet = _excelPackage.Workbook.Worksheets.Add(DefaultName_Worksheet);
            }
            else
            {
                _workSheet = _excelPackage.Workbook.Worksheets[0];
            }

            _dataTable = new DataTable();

        }

        public void Dispose()
        {
            // 将缓存中数据保存到文件
            if (_dataTable.Rows.Count > 0)
            {
                SaveDataToFiles_byDataTable();
            }

            //_excelPackage.Save();
            _excelPackage.Dispose();
            _dataTable.Clear();
        }

        // 选择表格页面
        public void SelWorkSheet(string name)
        {
            if (_excelPackage.Workbook.Worksheets.Any(x => x.Name == name))
            {
                _workSheet = _excelPackage.Workbook.Worksheets[name];
            }
            else
            {
                _workSheet = _excelPackage.Workbook.Worksheets.Add(name);
            }
        }

        // 方式1 按list写入
        public void AddRow(List<string> rowContent)
        {
            if (rowContent.Count == 0) return;

            CheckAndChangeToNawFile();

            int rowNo = _workSheet.Rows.Count() + 1;
            _workSheet.Cells[rowNo, 1, rowNo, rowContent.Count].FillList(rowContent, x =>
            {
                x.Direction = eFillDirection.Row;
            });

            CheckToSave(1);
        }
        // 方式1 按list写入
        public void AddRow(List<double> rowContent)
        {
            if (rowContent.Count == 0) return;

            CheckAndChangeToNawFile();

            int rowNo = _workSheet.Rows.Count() + 1;
            _workSheet.Cells[rowNo, 1, rowNo, rowContent.Count].FillList(rowContent, x =>
            {
                x.Direction = eFillDirection.Row;
            });

            CheckToSave(1);
        }

        // 方式2 按datatable写入
        public void AddRow_ByDataTable(List<double> rowContent)
        {
            // 检查已有行数是否超过上限，超过就切换到新文件。
            if (_workSheet.Rows.Count() >= MaxRowNum_AddRows_byDataTable)
            {
                ChangeToNawFile();
            }

            // 检查缓存_dataTable的列数是否大于等于rowContent长度。
            if (_dataTable.Columns.Count < rowContent.Count)
            {
                // 如果小了，就扩张_dataTable的列数
                for (int i = _dataTable.Columns.Count; i < rowContent.Count; i++)
                {
                    _dataTable.Columns.Add("col" + i.ToString(), typeof(String));
                }
            }

            // 将数据添加到缓存_dataTable
            DataRow dr = _dataTable.NewRow();
            for (int i = 0; i < rowContent.Count; i++)
                dr[i] = rowContent[i].ToString();

            _dataTable.Rows.Add(dr);


            // 如果缓存_dataTable的行数大于等于缓存上限行数，就调用保存方法。
            //if (_dataTable.Rows.Count >= MaxRowCount_NeedToSave_AddRows_byDataTable)
            //{
            //    SaveDataToFiles_byDataTable();
            //}
        }

        private async void SaveDataToFiles_byDataTable()
        {
            int rowNo = _workSheet.Rows.Count() + 1;
            _workSheet.Cells[rowNo, 1].LoadFromDataTable(_dataTable);
            await _excelPackage.SaveAsync();

            _dataTable.Rows.Clear();
        }

        public async void AddRows(DataTable datas)
        {
            CheckAndChangeToNawFile();

            int rowNo = _workSheet.Rows.Count() + 1;
            _workSheet.Cells[rowNo, 1].LoadFromDataTable(datas);
            await _excelPackage.SaveAsync();
        }

        #region 私有成员

        private string _dirPath;
        private string _fileName;
        private ExcelPackage _excelPackage;
        private ExcelWorksheet _workSheet;
        private int _fileNo; // 如果数据太多，就要分文件存储，所以这里记录文件序号
        private const int MaxRowNum_AddRow = 200;   // addrow形式写200行，耗时1。9s
        private const int MaxRowNum_AddRows_byDataTable = 1000; /// datatable形式写1000行，耗时0.7s。 
        private static string DefaultName_Worksheet = "Sheet1";

        private int hadAddRowCount = 0;
        private int MaxRowCount_NeedToSave = 100;
        private const int MaxRowCount_NeedToSave_AddRows_byDataTable = 300;

        private DataTable _dataTable;

        private bool _disposed;


        // 记录添加行的数量，并及时保存到文件。
        private async void CheckToSave(int rowCount)
        {
            hadAddRowCount += rowCount;
            if (hadAddRowCount >= MaxRowCount_NeedToSave)
            {
                hadAddRowCount = 0;
                await _excelPackage.SaveAsync();
            }
        }

        private void CheckAndChangeToNawFile()
        {
            if (_workSheet.Rows.Count() >= MaxRowNum_AddRow)
            {
                ChangeToNawFile();
            }
        }

        // 如果数据太多，就要分文件存储,写excel文件耗时较长
        private void ChangeToNawFile()
        {
            _fileNo += 1;
            string name, ex;
            int idx = _fileName.LastIndexOf(".");
            if (idx == -1)
            {
                name = _fileName;
                ex = "xlsx";
            }
            else
            {
                name = _fileName.Substring(0, idx);
                ex = _fileName.Substring(idx);
            }

            _excelPackage = new ExcelPackage(new FileInfo(_dirPath + "\\" + name + "(" + (_fileNo).ToString() + ")" + ex));
            _workSheet = _excelPackage.Workbook.Worksheets.Add(DefaultName_Worksheet);
        }

        #endregion  
    }
}
